package cn.pzhu.java.ch06.dbapp;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class UserDao {
        final static String url = "jdbc:mysql://localhost:3306/javadb?useUnicode=true&characterEncoding=UTF-8";
        final static String user = "root";
        final static String password = "123456";
        static {
                try {
                        Class.forName("com.mysql.jdbc.Driver");
                } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                }
        }

        private Connection getConnection() throws SQLException {
                Connection conn = DriverManager.getConnection(url, user, password);
                return conn;
        }
        public boolean updateUser(User user){
                boolean flag=false;
                String sql="UPDATE Users SET NAME=?,AGE=? WHERE ID=?";
                Connection conn=null;
                PreparedStatement pst=null;
                try {
                        conn=getConnection();
                        pst=conn.prepareStatement(sql);
                        pst.setString(1,user.getName());
                        pst.setInt(2, user.getAge());
                        pst.setLong(3, user.getId());
                        flag=(pst.executeUpdate()==1);
                        
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }finally{
                        try {
                                if(pst!=null)pst.close();
                                if(conn!=null)conn.close();
                        } catch (SQLException e) {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                        }
                }
                
                return flag;
        }
        public User  queryUserById(long id){
                User user=null;
                String sql="SELECT  ID,NAME,AGE FROM Users WHERE ID=?";
                Connection conn=null;
                PreparedStatement pst=null;
                ResultSet rs=null;
                
                try {
                        conn=getConnection();
                        pst=conn.prepareStatement(sql);
                        pst.setLong(1, id);
                        rs=pst.executeQuery();
                        if(rs.next()){
                                user=new User(rs.getLong("ID"),
                                                rs.getString("NAME"),
                                                rs.getInt("AGE"));
                        }
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }finally{
                        try {
                                if(rs!=null)rs.close();
                                if(pst!=null)pst.close();
                                if(conn!=null)conn.close();
                        } catch (SQLException e) {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                        }
                }
                return user;
                
        }
        public boolean addUser(User user){
                boolean flag=false;
                String sql="INSERT INTO Users(NAME,AGE)VALUES(?,?)";
                Connection conn=null;
                PreparedStatement pst=null;
                try {
                        conn=getConnection();
                        pst=conn.prepareStatement(sql);
                        pst.setString(1,user.getName());
                        pst.setInt(2, user.getAge());
                        flag=(pst.executeUpdate()==1);
                        
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }finally{
                        try {
                                if(pst!=null)pst.close();
                                if(conn!=null)conn.close();
                        } catch (SQLException e) {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                        }
                }
                
                return flag;
        }
        public List<User>  queryUserByName(String name){
                List<User> userList=new ArrayList<User>();
                String sql="SELECT  ID,NAME,AGE FROM Users WHERE NAME  LIKE ?";
                Connection conn=null;
                PreparedStatement pst=null;
                ResultSet rs=null;
                
                try {
                        conn=getConnection();
                        pst=conn.prepareStatement(sql);
                        pst.setString(1, "%"+name+"%");
                        rs=pst.executeQuery();
                        while(rs.next()){
                                User u=new User(rs.getLong("ID"),
                                                rs.getString("NAME"),
                                                rs.getInt("AGE"));
                                userList.add(u);
                        }
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }finally{
                        try {
                                if(rs!=null)rs.close();
                                if(pst!=null)pst.close();
                                if(conn!=null)conn.close();
                        } catch (SQLException e) {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                        }
                }
                return userList;
                
        }
        public List<User>  getAllUser(){
            List<User> userList=new ArrayList<User>();
            String sql="SELECT  ID,NAME,AGE FROM Users";
            Connection conn=null;
            PreparedStatement pst=null;
            ResultSet rs=null;
            
            try {
                    conn=getConnection();
                    pst=conn.prepareStatement(sql);
                   
                    rs=pst.executeQuery();
                    while(rs.next()){
                            User u=new User(rs.getLong("ID"),
                                            rs.getString("NAME"),
                                            rs.getInt("AGE"));
                            userList.add(u);
                    }
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            }finally{
                    try {
                            if(rs!=null)rs.close();
                            if(pst!=null)pst.close();
                            if(conn!=null)conn.close();
                    } catch (SQLException e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                    }
            }
            return userList;
            
    }
}